package com.guofs.project.common.utils.excel;

import com.guofs.project.common.utils.excel.annotation.AnnExcelColumn;
import lombok.AllArgsConstructor;
import lombok.Data;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.*;


@Data
@AllArgsConstructor
class ExcelUseField {
    /** 在列上是列名，在类上是sheet的名 */
    private String fieldName;
    private Class fieldType;
}

/**
 * @author GuoFangshi
 * @date 2019/1/7 15:12
 */
public class ExcelUtils {

    /**
     * Get the object collection according to the Excel file stream
     * @param fileName file Name
     * @param sheetName sheet Name. it is only empty, take the first sheet by default.
     * @param in Excel file stream
     * @param clazz Object class. mast use @AnnExcelColumn(com.guofs.learn.springbootdemo.util.excel.annotation.AnnExcelColumn)
     * @param <T> Object Generic
     * @return object collection
     * @throws Exception Transfer exceptions to the outside for unified logging
     */
    public static <T> List<T> importExcelToObjectList(String fileName, String sheetName, InputStream in, Class<T> clazz)
            throws Exception {
        if (in == null || clazz == null) {
            throw new NullPointerException("Input stream and output types cannot be null!");
        }
        List<T> list = new ArrayList<T>();
        Workbook wb;
        // Verify the version of excel
        if (isExcel2003(fileName)) {
            wb = new HSSFWorkbook(in);
        } else {
            wb = new XSSFWorkbook(in);
        }
        // Gets the data in the (specified) sheet page
        Sheet sheet = sheetName != null ? wb.getSheet(sheetName) : wb.getSheetAt(0);
        // Import export column sort
        List<ExcelUseField> columnNames = getSortColumnByClass(clazz);
        // Start parsing excel data
        boolean sheetNotNull = sheet != null;
        T obj;
        Object cellVal;
        // 空值计数
        int emptyNum;
        for (int line = 1; sheetNotNull && line <= sheet.getLastRowNum(); line++) {
            emptyNum = 0;
            Row row = sheet.getRow(line);
            if (row == null) {
                continue;
            }
            obj = clazz.newInstance();

            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                cellVal = null;
                if (cell == null || cell.getCellType() == CellType.BLANK) {
                    emptyNum++;
                    continue;
                }
                ExcelUseField excelUseField = columnNames.get(cell.getColumnIndex());
                String getMethodName = "set" + excelUseField.getFieldName().substring(0, 1).toUpperCase() +
                        excelUseField.getFieldName().substring(1);
                Class<?> fieldType = excelUseField.getFieldType();
                Method setMethod = clazz.getDeclaredMethod(getMethodName, fieldType);

                if (CellType.NUMERIC.equals(cell.getCellType())) {
                    cellVal = cell.getNumericCellValue();
                } else if (CellType.BOOLEAN.equals(cell.getCellType())) {
                    cellVal = cell.getBooleanCellValue();
                } else if (CellType.STRING.equals(cell.getCellType())) {
                    cellVal = String.valueOf(cell.getRichStringCellValue());
                } else if (CellType.FORMULA.equals(cell.getCellType())) {
                    throw new Exception("无法识别公式型的值");
                } else if (CellType.ERROR.equals(cell.getCellType())) {
                    throw new Exception("无法识别错误的值");
                }

                if (fieldType.isAssignableFrom(String.class)) {
                    setMethod.invoke(obj, String.valueOf(cellVal));
                } else if (fieldType.isAssignableFrom(boolean.class)) {
                    setMethod.invoke(obj, Boolean.valueOf(String.valueOf(cellVal)));
                } else if (fieldType.isAssignableFrom(double.class)) {
                    setMethod.invoke(obj, Double.valueOf(String.valueOf(cellVal)));
                } else if (fieldType.isAssignableFrom(Integer.class)) {
                    setMethod.invoke(obj, Double.valueOf(String.valueOf(cellVal)).intValue());
                } else if (fieldType.isAssignableFrom(BigDecimal.class)) {
                    setMethod.invoke(obj, BigDecimal.valueOf(Double.valueOf(String.valueOf(cellVal))));
                } else if (fieldType.isAssignableFrom(Date.class)) {
                    setMethod.invoke(obj, cell.getDateCellValue());
                } else {
                    setMethod.invoke(obj, String.valueOf(cell.getRichStringCellValue()));
                }
            }
            if (emptyNum == row.getLastCellNum()) {
                break;
            }
            list.add(obj);
        }
        return list;
    }

    /**
     * 导出excel列表
     * @param response 请求对象
     * @param fileName 文件名
     * @param list 列表数据
     * @param clazz 数据类型（使用@AnnExcelColumn(com.guofs.learn.springbootdemo.util.excel.annotation.AnnExcelColumn)）
     * @param <T> 数据类型（使用@AnnExcelColumn(com.guofs.learn.springbootdemo.util.excel.annotation.AnnExcelColumn)）
     * @throws Exception Transfer exceptions to the outside for unified logging
     */
    public static <T> void export(HttpServletResponse response,
                                  String fileName,
                                  List<T> list,
                                  Class<T> clazz) throws Exception {

        fileName = fileName + ".xlsx";
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition",
                "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));

        OutputStream out = null;
        XSSFWorkbook workbook = null;

        try {
            // 获取导出文件的sheet名
            ExcelUseField classProp = getClassProp(clazz);
            // 获取列名
            List<ExcelUseField> columnNames = getSortColumnByClass(clazz);

            out = response.getOutputStream();
            workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet(classProp == null ? "sheet" : classProp.getFieldName());

            // 开始写入数据
            int rowIndex = 0;
            int colIndex = 0;

            Row titleRow = sheet.createRow(rowIndex);

            // 放置表头
            for (ExcelUseField excelUseField: columnNames) {
                Cell cell = titleRow.createCell(colIndex);
                cell.setCellValue(excelUseField.getFieldName());
                colIndex++;
            }

            // 开始放置数据
            rowIndex++;

            for (T t: list) {
                Row dataRow = sheet.createRow(rowIndex);
                colIndex = 0;
                for (ExcelUseField excelUseField: columnNames) {
                    Cell cell = dataRow.createCell(colIndex);
                    String getMethodName = "get" +
                            excelUseField.getFieldName().substring(0, 1).toUpperCase() +
                            excelUseField.getFieldName().substring(1);
                    Method getMethod = clazz.getDeclaredMethod(getMethodName);
                    Object cellData = getMethod.invoke(t);
                    cell.setCellValue(cellData == null ? "" : cellData.toString());
                    colIndex++;
                }
                rowIndex++;
            }

            // 设置列宽
            for (int i = 0; i < columnNames.size() + 1; i++) {
                int orgWidth = sheet.getColumnWidth(i);
                sheet.autoSizeColumn(i, true);
                int newWidth = sheet.getColumnWidth(i) + 100;
                if (newWidth > orgWidth) {
                    sheet.setColumnWidth(i, newWidth);
                } else {
                    sheet.setColumnWidth(i, orgWidth);
                }
            }

            // 输出
            workbook.write(out);
        } catch (Exception e) {
            throw new Exception(e);
        } finally {
            if (workbook != null) {
                workbook.close();
            }
            if (out != null) {
                out.close();
            }
        }
    }

    /**
     * 获取类的定义名
     * @param clazz 类的类型
     * @param <T> 类的泛型
     * @return 类的定义的名字
     */
    private static <T> ExcelUseField getClassProp(Class<T> clazz) {
        if (clazz.isAnnotationPresent(AnnExcelColumn.class)) {
            AnnExcelColumn clazzAnn = clazz.getAnnotation(AnnExcelColumn.class);
            if (clazzAnn.title().trim().length() < 1) {
                return null;
            }
            return new ExcelUseField(clazzAnn.title(), null);
        }
        return null;
    }

    /**
     * 获取属性上的@AnnExcelColumn值
     * @param clazz 使用@AnnExcelColumn的类的类型
     * @param <T> 使用@AnnExcelColumn的类的泛型
     * @return 调整过的数据
     */
    private static <T> List<ExcelUseField> getSortColumnByClass(Class<T> clazz) {
        List<ExcelUseField> excelUseFields = new ArrayList<ExcelUseField>();
        List<Field> fieldList = new ArrayList<Field>();
        for (Field field: clazz.getDeclaredFields()) {
            if (!field.isAnnotationPresent(AnnExcelColumn.class)) {
                continue;
            }
            fieldList.add(field);
        }
        Collections.sort(fieldList, new Comparator<Field>() {
            @Override
            public int compare(Field now, Field next) {
                AnnExcelColumn nowColumn = now.getAnnotation(AnnExcelColumn.class);
                AnnExcelColumn nextColumn = next.getAnnotation(AnnExcelColumn.class);
                return nowColumn.order() - nextColumn.order();
            }
        });
        for (Field field: fieldList) {
            excelUseFields.add(new ExcelUseField(field.getName(), field.getType()));
        }

        return excelUseFields;
    }

    public static boolean isExcel2003(String fileName) {
        return fileName.matches("^.+\\.(?i)(xls)$");
    }
}